package com.travel.dao.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.json.JSONArray;

import com.travel.core.AbstractDao;
import com.travel.dao.ISpotDao;
import com.travel.entities.Spot;

/**
 * @author SAGITTAR
 */
public class SpotDaoImpl extends AbstractDao implements ISpotDao {

    private static final Logger LOGGER = LogManager.getLogger(SpotDaoImpl.class);

    @Override
    public List<Spot> getTopSpots(String loc, String orderBy, Direction direction) {
        if (Objects.isNull(direction)) {
            direction = Direction.DESC;
        }
        String sql = "SELECT AVG(a.rating) spotAvgRating, COUNT(1) spotRatingCount, b.spot_url spotUrl, a.spotId spotId, a.spotName spotName ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append("FROM tour a, spot b ");
        sqlBuilder.append("WHERE a.spotId = b.spot_id AND city = ? ");
        sqlBuilder.append("GROUP BY a.spotId ");
        sqlBuilder.append("ORDER BY %s %s LIMIT 0, 10");
        String preparedSql = String.format(sqlBuilder.toString(), orderBy, direction);
        List<Spot> spotList = selectListByClassTypeAndSql(Spot.class, preparedSql, loc);
        LOGGER.info(getLogInfo(true), preparedSql, Arrays.asList(loc));
        LOGGER.info(getResultLog(), spotList);
        return spotList;
    }

    @Override
    public List<Map<String, Object>> getStaticticsByYear(String cityCode, int from, int to) {
        String sql = "SELECT date_format(date, '%Y-%m') AS ym, COUNT(userId) AS count ";
        StringBuilder sqlBuider = new StringBuilder(sql);
        sqlBuider.append("FROM tour, spot ");
        sqlBuider.append("WHERE spotId = spot.spot_id AND spot.city = ? AND date >= ? AND date < ? ");
        sqlBuider.append("GROUP BY ym ");
        sqlBuider.append("ORDER BY ym");
        String preparedSql = sqlBuider.toString();
        Object[] params = new Object[] { cityCode, String.valueOf(from), String.valueOf(to) };
        List<Map<String, Object>> resultMapList = selectMapListBySqlAndParam(preparedSql, params);
        LOGGER.info(getLogInfo(true), sqlBuider, Arrays.asList(cityCode, from, to));
        LOGGER.info(getResultLog(), new JSONArray(resultMapList));
        return resultMapList;
    }

    @Override
    public List<Map<String, Object>> getStaticticsByCityOrSpotYear(String spotNameOrCityCode, List<String> years,
            int selectedType) {
        StringBuilder builder = new StringBuilder("SELECT date_format(date, '%Y-%m') AS ym, COUNT(userId) AS count ");
        builder.append("FROM tour, spot ");
        builder.append("WHERE spotId = spot.spot_id AND ");
        if (selectedType == 1) {
            builder.append("spotName = ? ");
        }
        if (selectedType == 2) {
            builder.append("city = ? ");
        }
        if (years.size() > 0) {
            builder.append("AND date_format(date, '%Y') IN(");
            builder.append("?");
            for (int i = 1; i < years.size(); i++) {
                builder.append(", ?");
            }
            builder.append(") ");
        }
        builder.append("GROUP BY ym");
        // 多一个参数为spotNameOrCityCode
        Object[] params = new Object[1 + years.size()];
        params[0] = spotNameOrCityCode;
        for (int index = 0; index < years.size(); index++) {
            params[index + 1] = years.get(index);
        }
        List<Map<String, Object>> resultMapList = selectMapListBySqlAndParam(builder.toString(), params);
        LOGGER.info(getLogInfo(true), builder, Arrays.asList(spotNameOrCityCode));
        LOGGER.info(getResultLog(), new JSONArray(resultMapList));
        return resultMapList;
    }

    @Override
    public int getStatisticsNumberByCityAndDate(String spotNameOrCityCode, int selectedType, String date) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT COUNT(1) ");
        sqlBuilder.append("FROM tour, spot ");
        sqlBuilder.append("WHERE tour.spotId = spot.spot_id ");
        if (selectedType == 1) {
            sqlBuilder.append("AND spotName = ? ");
        }
        if (selectedType == 2) {
            sqlBuilder.append("AND city = ? ");
        }
        if (StringUtils.isNotBlank(date)) {
            sqlBuilder.append("AND date LIKE ?");
        } else {
            sqlBuilder.append("AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(date)");
        }
        Double statistics;
        if (StringUtils.isNotBlank(date)) {
            statistics = selectNumberByClassTypeAndSql(sqlBuilder.toString(), spotNameOrCityCode, date + "%");
        } else {
            statistics = selectNumberByClassTypeAndSql(sqlBuilder.toString(), spotNameOrCityCode);
        }
        LOGGER.info(getLogInfo(true), sqlBuilder, Arrays.asList(spotNameOrCityCode));
        LOGGER.info(getResultLog(), statistics);
        return statistics.intValue();
    }

    @Override
    public int getStatisticsNumberByCityAndDate(String spotNameOrCityCode, int selectedType) {
        return getStatisticsNumberByCityAndDate(spotNameOrCityCode, selectedType, null);
    }
}
